/*
 * Copyright (C) 2009 2DAI
 *
 * Este programa es Software Libre; usted puede redistribuirlo
 * y/o modificarlo bajo los términos de la "GNU General Public
 * License" como lo publica la "FSF Free Software Foundation",
 * o (a su elección) de cualquier versión posterior.
 *
 * Este programa es distribuido con la esperanza de que le será
 * útil, pero SIN NINGUNA GARANTIA; incluso sin la garantía
 * implícita por el MERCADEO o EJERCICIO DE ALGUN PROPOSITO en
 * particular. Vea la "GNU General Public License" para más
 * detalles.
 *
 * Usted debe haber recibido una copia de la "GNU General Public
 * License" junto con este programa, si no, escriba a la "FSF
 * Free Software Foundation, Inc.", 59 Temple Place - Suite 330,
 * Boston, MA  02111-1307, USA.
 */

package data;

import com.mysql.jdbc.ResultSet;
import java.sql.SQLException;
import on.ClubRepresentatives;
import util.Errors;
/**
 *
 * Author: Francisco Pardo
 */
public class DataClubRepresentatives extends DataManager<ClubRepresentatives> {

    private String tabla = "club_representatives";
    private String tabla2 = "club";

    public DataClubRepresentatives() {
        super();
        setTabla(tabla);
        requerir();
    }

    @Override
    public void requerir() {
        try {
            //conectamos
            sql.conectar();
            //recojemos los resultados. En la select utilizanos la variable que
            //contiene el nombre de la tabla.
            ResultSet rs = (ResultSet) sql.ejecutarSelect("SELECT id_club_representatives," +
                    "num_licence,last_name,first_name," + tabla +".dni," +
                    "postal_code," + tabla +".address," + tabla +".city," +
                     tabla +".province," + tabla +".phone," + tabla + ".email,"+
                    "class,name" + " FROM " + tabla+","+tabla2+
                    " WHERE "+tabla+".id_club="+tabla2+".id_club");
            //mientras hayan resultados los vamos insertando en el array de jugadores
            while (rs.next()) {
                ClubRepresentatives cr = new ClubRepresentatives();
                cr.setId_RepresentantesClub(rs.getInt(1));
                cr.setNum_licencia(rs.getInt(2));
                cr.setApellidos(rs.getString(3));
                cr.setNombre(rs.getString(4));
                cr.setDni(rs.getInt(5));
                cr.setCodigoPostal(rs.getInt(6));
                cr.setDireccion(rs.getString(7));
                cr.setPoblacion(rs.getString(8));
                cr.setProvincia(rs.getString(9));
                cr.setTelefono(rs.getInt(10));
                cr.setEMail(rs.getString(11));
                cr.setClase(rs.getString(12));
                cr.setClub(rs.getString(13));
                getList().add(cr);
            }
        } catch (SQLException ex) {
            Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
        } finally {
            sql.desconectar();
        }

    }

    @Override
    public void guardar(ClubRepresentatives o) {
        getList().set(index, o);
        sql.conectar();
        sql.ejectarUpdate("UPDATE " + tabla + " " +
                "SET num_licence=" + o.getNum_licencia() + "," +
                "last_name='" + o.getApellidos() + "'," +
                "first_name='" + o.getNombre() + "'," +
                "dni='" + o.getDni() + "'," +
                "postal_code='" + o.getCodigoPostal() + "'," +
                "address='" + o.getDireccion() + "'," +
                "city='" + o.getPoblacion() + "'," +
                "province='" + o.getProvincia() + "'," +
                "phone='" + o.getTelefono() + "'," +
                "email='" + o.getEMail() + "'," +
                "class='" + o.getClase() + "'," +
                "id_club=" + o.getId_club() +
                " WHERE id_club_representatives=" + o.getId_RepresentantesClub());
        sql.desconectar();
    }

    @Override
    public void insertar(ClubRepresentatives o) {
        sql.conectar();
        getList().add(o);
        sql.ejectarUpdate("INSERT INTO " + tabla + " (id_club,num_licence," +
                "last_name,first_name, dni, postal_code, address," +
                " city, province, phone, email, class) " +
                "VALUES(" + o.getId_club() + "," + o.getNum_licencia() + "," +
                "\'" + o.getApellidos() + "\',\'" + o.getNombre() + "\'," +
                "\'" + o.getDni() + "\'," +
                "\'" + o.getCodigoPostal() + "\',\'" + o.getDireccion() + "\'," +
                "\'" + o.getPoblacion() + "\',\'" + o.getProvincia() + "\'," +
                "\'" + o.getTelefono() + "\',\'" + o.getEMail() + "\'," +
                "\'" +  o.getClase() + "\')");
        sql.desconectar();
    }

    @Override
    public void eliminar(ClubRepresentatives o) {
        sql.conectar();
        getList().remove(index);
        sql.ejectarUpdate("DELETE FROM " + tabla + " " +
                "WHERE id_club_representatives = " + o.getId_RepresentantesClub());
        sql.desconectar();
    }

    /**
     * método que pasandole el id del jugador nos devuelve toda su información
     * @param id
     * @return
     */
    public ClubRepresentatives obtenerRepresentanteClub(int id) {
        ClubRepresentatives cr = new ClubRepresentatives();
        try {
            sql.conectar();
            //getList().remove(index);
            ResultSet rs = (ResultSet) sql.ejecutarSelect("SELECT * FROM " + tabla +
                    " " + "SET id_club_representatives=" + id);
            rs.next();

            cr.setId_club(rs.getInt(2));
            cr.setNum_licencia(rs.getInt(3));
            cr.setApellidos(rs.getString(4));
            cr.setNombre(rs.getString(5));
            cr.setDni(rs.getInt(6));
            cr.setCodigoPostal(rs.getInt(7));
            cr.setDireccion(rs.getString(8));
            cr.setPoblacion(rs.getString(9));
            cr.setProvincia(rs.getString(10));
            cr.setTelefono(rs.getInt(11));
            cr.setEMail(rs.getString(12));
            cr.setClase(rs.getString(13));

        } catch (SQLException ex) {
            Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
        } finally {
            sql.desconectar();
            return cr;
        }
    }

    @Override
    public void crear(String tabla) {
        sql.conectar();
        sql.ejectarUpdate("DROP TABLE IF EXISTS " + tabla + ";");
        sql.ejectarUpdate("CREATE TABLE IF NOT EXISTS " + tabla + " (" +
                "id_club_representatives int(11) NOT NULL auto_increment," +
                "num_licence int(11) NOT NULL," +
                "last_name varchar(20) NOT NULL," +
                "first_name varchar(20) NOT NULL," +
                "dni int(8) NOT NULL," +                
                "postal_code int(6) NOT NULL," +
                "address varchar(30) NOT NULL," +
                "city varchar(30) NOT NULL," +
                "province varchar(30) NOT NULL," +
                "phone int(11) default NULL," +
                "email varchar(40) default NULL," +
                "class varchar(3) NOT NULL," +
                "PRIMARY KEY  (id_club_representatives) )" +
                "ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
        sql.desconectar();
    }
}
